using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using OgilvyOne.Model;
using OgilvyOne.IDAL;
using System.Collections;
using OgilvyOne.Utility;

namespace OgilvyOne.SqlServerDAL
{
    public class SOgilvyeCard : IOgilvyeCard
    {
        // Methods
        public bool DeleteByIdentity(int id)
        {
            bool flag = false;
            int num = 0;
            string cmdText = "";
            cmdText = "DELETE FROM ogilvyeCard WHERE id=@id";
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@id", SqlDbType.Int, 10) };
            cmdParms[0].Value = id;
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        num = SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, cmdText, cmdParms);
                        transaction.Commit();
                        if (num > 0)
                        {
                            flag = true;
                        }
                    }
                    catch (SqlException exception)
                    {
                        transaction.Rollback();
                        Console.WriteLine(exception.Message);
                    }
                    return flag;
                }
            }
        }

        public MOgilvyeCard GetRowByIdentity(int id)
        {
            MOgilvyeCard card = null;
            string cmdText = "";
            cmdText = "SELECT id,beatitude,puppet_txt,myname,mymail,toname1,tomail1,toname2,tomail2,toname3,tomail3,isrdt FROM ogilvyeCard WHERE id=@id";
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@id", SqlDbType.Int, 10) };
            cmdParms[0].Value = id;
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, cmdParms))
            {
                while (reader.Read())
                {
                    try
                    {
                        card = new MOgilvyeCard();
                        card.Id = Convert.ToInt32(reader[0]);
                        card.Beatitude = Convert.ToString(reader[1]);
                        card.Puppet_txt = Convert.ToString(reader[2]);
                        card.Myname = Convert.ToString(reader[3]);
                        card.Mymail = Convert.ToString(reader[4]);
                        card.Toname1 = Convert.ToString(reader[5]);
                        card.Tomail1 = Convert.ToString(reader[6]);
                        card.Toname2 = Convert.ToString(reader[7]);
                        card.Tomail2 = Convert.ToString(reader[8]);
                        card.Toname3 = Convert.ToString(reader[9]);
                        card.Tomail3 = Convert.ToString(reader[10]);
                        card.Isrdt = Convert.ToString(reader[11]);
                    }
                    catch (SqlException exception)
                    {
                        Console.WriteLine(exception.Message);
                    }
                }
            }
            return card;
        }

        public DataSet GetRows(string condition)
        {
            DataSet dataSet = new DataSet();
            string str = "SELECT id,beatitude,puppet_txt,myname,mymail,toname1,tomail1,toname2,tomail2,toname3,tomail3,isrdt FROM ogilvyeCard " + condition;
            using (SqlDataAdapter adapter = new SqlDataAdapter())
            {
                try
                {
                    adapter.SelectCommand = new SqlCommand();
                    adapter.SelectCommand.CommandText = str;
                    adapter.SelectCommand.Connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC);
                    adapter.Fill(dataSet);
                }
                catch (SqlException exception)
                {
                    Console.WriteLine(exception.Message);
                }
            }
            return dataSet;
        }

        public int Insert(MOgilvyeCard data)
        {
            int num = 0;
            string cmdText = "INSERT INTO ogilvyeCard(beatitude,puppet_txt,myname,mymail,toname1,tomail1,toname2,tomail2,toname3,tomail3) VALUES(@beatitude, @puppet_txt, @myname, @mymail, @toname1, @tomail1, @toname2, @tomail2, @toname3, @tomail3)";
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@beatitude", SqlDbType.NVarChar, 500), new SqlParameter("@puppet_txt", SqlDbType.NVarChar, 500), new SqlParameter("@myname", SqlDbType.NVarChar, 50), new SqlParameter("@mymail", SqlDbType.NVarChar, 200), new SqlParameter("@toname1", SqlDbType.NVarChar, 50), new SqlParameter("@tomail1", SqlDbType.NVarChar, 200), new SqlParameter("@toname2", SqlDbType.NVarChar, 50), new SqlParameter("@tomail2", SqlDbType.NVarChar, 200), new SqlParameter("@toname3", SqlDbType.NVarChar, 50), new SqlParameter("@tomail3", SqlDbType.NVarChar, 200) };
            cmdParms[0].Value = data.Beatitude;
            cmdParms[1].Value = data.Puppet_txt;
            cmdParms[2].Value = data.Myname;
            cmdParms[3].Value = data.Mymail;
            cmdParms[4].Value = data.Toname1;
            cmdParms[5].Value = data.Tomail1;
            cmdParms[6].Value = data.Toname2;
            cmdParms[7].Value = data.Tomail2;
            cmdParms[8].Value = data.Toname3;
            cmdParms[9].Value = data.Tomail3;
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        num = SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, cmdText, cmdParms);
                        transaction.Commit();
                    }
                    catch (SqlException exception)
                    {
                        transaction.Rollback();
                        Console.WriteLine(exception.Message);
                    }
                    return num;
                }
            }
        }

        public bool ModifyByIdentity(MOgilvyeCard data)
        {
            bool flag = false;
            int num = 0;
            string cmdText = "";
            cmdText = "UPDATE ogilvyeCard SET beatitude=@beatitude,puppet_txt=@puppet_txt,myname=@myname,mymail=@mymail,toname1=@toname1,tomail1=@tomail1,toname2=@toname2,tomail2=@tomail2,toname3=@toname3,tomail3=@tomail3 WHERE id=@id";
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@id", SqlDbType.Int, 10), new SqlParameter("@beatitude", SqlDbType.NVarChar, 500), new SqlParameter("@puppet_txt", SqlDbType.NVarChar, 200), new SqlParameter("@myname", SqlDbType.NVarChar, 50), new SqlParameter("@mymail", SqlDbType.NVarChar, 200), new SqlParameter("@toname1", SqlDbType.NVarChar, 50), new SqlParameter("@tomail1", SqlDbType.NVarChar, 200), new SqlParameter("@toname2", SqlDbType.NVarChar, 50), new SqlParameter("@tomail2", SqlDbType.NVarChar, 200), new SqlParameter("@toname3", SqlDbType.NVarChar, 50), new SqlParameter("@tomail3", SqlDbType.NVarChar, 200) };
            cmdParms[0].Value = data.Id;
            cmdParms[1].Value = data.Beatitude;
            cmdParms[2].Value = data.Puppet_txt;
            cmdParms[3].Value = data.Myname;
            cmdParms[4].Value = data.Mymail;
            cmdParms[5].Value = data.Toname1;
            cmdParms[6].Value = data.Tomail1;
            cmdParms[7].Value = data.Toname2;
            cmdParms[8].Value = data.Tomail2;
            cmdParms[9].Value = data.Toname3;
            cmdParms[10].Value = data.Tomail3;
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        num = SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, cmdText, cmdParms);
                        transaction.Commit();
                        if (num > 0)
                        {
                            flag = true;
                        }
                    }
                    catch (SqlException exception)
                    {
                        transaction.Rollback();
                        Console.WriteLine(exception.Message);
                    }
                    return flag;
                }
            }
        }
    }
}